﻿using Coldairarrow.Entity.Base_Manage;
using Coldairarrow.Entity.DTO;
using Coldairarrow.Entity.PB;
using Coldairarrow.IBusiness;
using Coldairarrow.Util;
using EFCore.Sharding;
using LinqKit;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Text;
using System.Threading.Tasks;

namespace Coldairarrow.Business.PB
{
    public partial class PB_UserWagesBusiness : BaseBusiness<PB_UserWages>, IPB_UserWagesBusiness, ITransientDependency
    {
        readonly IOperator _operator;
        readonly IOperatorDept _opdept;
        public PB_UserWagesBusiness(IDbAccessor db, IOperator @operator, IOperatorDept @opdept)
            : base(db)
        {
            _operator = @operator;
            _opdept = @opdept;
        }

        #region 外部接口

        public async Task<PageResult<PB_UserWages>> GetDataListAsync(PageInput<ConditionDTO> input)
        {
            var q = GetIQueryable();
            var where = LinqHelper.True<PB_UserWages>();
            var search = input.Search;

            //筛选
            if (!search.Condition.IsNullOrEmpty() && !search.Keyword.IsNullOrEmpty())
            {
                var newWhere = DynamicExpressionParser.ParseLambda<PB_UserWages, bool>(
                    ParsingConfig.Default, false, $@"{search.Condition}.Contains(@0)", search.Keyword);
                where = where.And(newWhere);
            }

            return await q.Where(where).GetPageResultAsync(input);
        }

        public async Task<PB_UserWages> GetTheDataAsync(string id)
        {
            return await GetEntityAsync(id);
        }

        public async Task AddDataAsync(PB_UserWages data)
        {
            await InsertAsync(data);
        }

        public async Task UpdateDataAsync(PB_UserWages data)
        {
            await UpdateAsync(data);
        }

        public async Task DeleteDataAsync(List<string> ids)
        {
            await DeleteAsync(ids);
        }

        /// <summary>
        /// 验证工资是否有结算数据
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public async Task<int> CheckUserWageDataAsync(WagesDTO data) 
        {
            var list = await GetDataTableWithSqlAsync("select count(*) from PB_UserWages where DeptId='" + data.DeptId + "' and WorkDate BETWEEN '" + data.StartDate + "' and '" + data.EndDate + "' ");
            return Convert.ToInt32(list.Rows[0][0]);
        }

        /// <summary>
        /// 计算工资计件(按车间)
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public async Task UpUserWagesForNumAsync(WagesDTO data)
        {
            //删除已经统计过的数据
            await ExecuteSqlAsync("delete from PB_UserWages where DeptId='" + data.DeptId + "' and WorkDate BETWEEN '" + data.StartDate + "' and '" + data.EndDate + "' ");
            await ExecuteSqlAsync("delete from PB_UserWages where Type=2 and WorkDate BETWEEN '" + data.StartDate + "' and '" + data.EndDate + "' ");
            #region  获取符合条件的报工（统计数据）
            StringBuilder sql=new StringBuilder();
            sql.Append("SELECT ");
            sql.Append("a.FactoryId,");
            sql.Append("a.DeptId,");
            sql.Append("c.Type,");
            sql.Append("d.Name TypeName,");
            sql.Append("a.ProcedureId,");
            //sql.Append("c.CreatorRealName,");
            sql.Append("b.RealName CreatorRealName,");
            sql.Append("g.Name MaterialName,");
            sql.Append("Date(a.StatisticalTime) StatisticalTime,");
            sql.Append("a.StatisticalQuantity Num,");
            sql.Append("ifnull(f.UnitPrice,0) UnitPrice,");
            sql.Append("(a.StatisticalQuantity*ifnull(f.UnitPrice,0)) Total ");
            sql.Append("FROM ");
            sql.Append("PB_CirculationBill a ");
            sql.Append("LEFT JOIN Base_User b on a.WorkerId=b.Id ");
            //sql.Append("LEFT JOIN PB_ScheduleDetail c on a.WorkerId=c.UserId AND Date( a.StatisticalTime )=c.WorkDate ");
            sql.Append("LEFT JOIN PB_ScheduleDetail c on a.WorkerId=c.UserId AND (Date( a.StatisticalTime )=c.WorkDate or (TO_DAYS(a.StatisticalTime) - TO_DAYS(c.WorkDate)) = 1)");
            sql.Append("LEFT JOIN PB_ShiftType d on c.Type=d.Id ");
            //sql.Append("LEFT JOIN PB_MaterialPrice f on a.MaterialId=f.Materialid AND a.ProcedureId=f.ProcecureSubid AND Date( a.StatisticalTime )=f.WorkDate AND a.DeptId=f.Deptid ");
            sql.Append("LEFT JOIN PB_MaterialPrice f on a.MaterialId=f.Materialid AND a.ProcedureId=f.ProcecureSubid AND Date( a.StatisticalTime )=f.WorkDate ");
            sql.Append("RIGHT JOIN (select * from PB_Material where IsProduct=0) g on a.MaterialId=g.Id ");
            sql.Append("WHERE ");
            sql.Append("a.DeptId = '"+ data.DeptId + "' ");
            sql.Append("AND Date( a.StatisticalTime ) BETWEEN '" + data.StartDate + "' AND '" + data.EndDate + "' ");
            var listdata = await Db.GetDataTableWithSqlAsync(sql.ToString());
            #endregion


            List<PB_UserWages> wagesdata = new List<PB_UserWages>();
            foreach (DataRow dr in listdata.Rows)
            {
                PB_UserWages wages = new PB_UserWages();
                wages.Id = IdHelper.GetId();
                wages.FactoryId = dr["FactoryId"].ToString();
                wages.DeptId = dr["DeptId"].ToString();
                wages.UserName = dr["CreatorRealName"].ToString();
                wages.TeamType = dr["Type"].ToString();
                wages.TeamName = dr["TypeName"].ToString();
                wages.SubId = dr["ProcedureId"].ToString();
                wages.WorkDate = Convert.ToDateTime(dr["StatisticalTime"].ToString());
                wages.Price = Convert.ToDouble(dr["UnitPrice"]);
                wages.Nums = Convert.ToDouble(dr["Num"]);
                wages.TotalPrice = Convert.ToDouble(dr["Total"]);
                wages.CreatorId = _operator.UserId;
                wages.CreatorRealName = _operator.UserName;
                wages.CreateTime = DateTime.Now;
                wages.Deleted = false;
                wages.Type = 0;
                wages.PayMode = 0;
                wages.MaterialName = dr["MaterialName"].ToString();
                wagesdata.Add(wages);
            }
            await Db.InsertAsync<PB_UserWages>(wagesdata);
        }

        /// <summary>
        /// 计算工资计重(按车间)
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public async Task UpUserWagesForWeightAsync(WagesDTO data)
        {
            #region  获取符合条件的报工（统计数据）
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT ");
            sql.Append("a.FactoryId,");
            sql.Append("a.DeptId,");
            sql.Append("c.Type,");
            sql.Append("d.Name TypeName,");
            sql.Append("a.ProcedureId,");
            //sql.Append("c.CreatorRealName,");
            sql.Append("b.RealName CreatorRealName,");
            sql.Append("g.Name MaterialName,");
            sql.Append("Date(a.StatisticalTime) StatisticalTime,");
            sql.Append("a.StatisticalQuantity*ifnull(g.Weight,0) Num,");
            sql.Append("ifnull(f.UnitPrice,0) UnitPrice,");
            sql.Append("(a.StatisticalQuantity*ifnull(g.Weight,0)*ifnull(f.UnitPrice,0)) Total ");
            sql.Append("FROM ");
            sql.Append("PB_CirculationBill a ");
            sql.Append("LEFT JOIN Base_User b on a.WorkerId=b.Id ");
            //sql.Append("LEFT JOIN PB_ScheduleDetail c on a.WorkerId=c.UserId AND Date( a.StatisticalTime )=c.WorkDate ");
            sql.Append("LEFT JOIN PB_ScheduleDetail c on a.WorkerId=c.UserId AND (Date( a.StatisticalTime )=c.WorkDate or (TO_DAYS(a.StatisticalTime) - TO_DAYS(c.WorkDate)) = 1)");
            sql.Append("LEFT JOIN PB_ShiftType d on c.Type=d.Id ");
            //sql.Append("LEFT JOIN PB_MaterialPrice f on a.MaterialId=f.Materialid AND a.ProcedureId=f.ProcecureSubid AND Date( a.StatisticalTime )=f.WorkDate AND a.DeptId=f.Deptid ");
            sql.Append("LEFT JOIN PB_MaterialPrice f on a.MaterialId=f.Materialid AND a.ProcedureId=f.ProcecureSubid AND Date( a.StatisticalTime )=f.WorkDate ");
            sql.Append("RIGHT JOIN (select * from PB_Material where IsProduct=1) g on a.MaterialId=g.Id ");
            sql.Append("WHERE ");
            sql.Append("a.DeptId = '" + data.DeptId + "' ");
            sql.Append("AND Date( a.StatisticalTime ) BETWEEN '" + data.StartDate + "' AND '" + data.EndDate + "' ");
           
            var listdata = await Db.GetDataTableWithSqlAsync(sql.ToString());
            #endregion


            List<PB_UserWages> wagesdata = new List<PB_UserWages>();
            foreach (DataRow dr in listdata.Rows)
            {
                PB_UserWages wages = new PB_UserWages();
                wages.Id = IdHelper.GetId();
                wages.FactoryId = dr["FactoryId"].ToString();
                wages.DeptId = dr["DeptId"].ToString();
                wages.UserName = dr["CreatorRealName"].ToString();
                wages.TeamType = dr["Type"].ToString();
                wages.TeamName = dr["TypeName"].ToString();
                wages.SubId = dr["ProcedureId"].ToString();
                wages.WorkDate = Convert.ToDateTime(dr["StatisticalTime"].ToString());
                wages.Price = Convert.ToDouble(dr["UnitPrice"]);
                wages.Nums = Convert.ToDouble(dr["Num"]);
                wages.TotalPrice = Convert.ToDouble(dr["Total"]);
                wages.CreatorId = _operator.UserId;
                wages.CreatorRealName = _operator.UserName;
                wages.CreateTime = DateTime.Now;
                wages.Deleted = false;
                wages.Type = 1;
                wages.PayMode = 0;
                wages.MaterialName = dr["MaterialName"].ToString();
                wagesdata.Add(wages);
            }
            await Db.InsertAsync<PB_UserWages>(wagesdata);
        }

        /// <summary>
        /// 计算工资喷涂(按车间)
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public async Task UpUserWagesForPaintingAsync(WagesDTO data)
        {
            #region  获取符合条件的报工（统计数据）
            StringBuilder sql = new StringBuilder();
            #region 旧sql
            //sql.Append("SELECT ");
            //sql.Append("t.ReportUserId,");
            //sql.Append("t.FactoryId,");
            //sql.Append("t.DeptId,");
            //sql.Append("t.Type,");
            //sql.Append("t.TypeName,");
            //sql.Append("t.ProcedureId,");
            //sql.Append("t.CreatorRealName,");
            //sql.Append("t.MaterialName,");
            //sql.Append("t.StatisticalTime,");
            //sql.Append("sum(t.Num) Num,");
            //sql.Append("t.UnitPrice,");
            //sql.Append("sum(Total) Total ");
            //sql.Append("FROM (");
            //sql.Append("SELECT ");
            //sql.Append("a.ReportUserId,");
            //sql.Append("b.FactoryId,");
            //sql.Append("a.DeptId,");
            //sql.Append("c.Type,");
            //sql.Append("g.Name MaterialName,");
            //sql.Append("d.NAME TypeName,");
            //sql.Append("'1463792840462045184' ProcedureId,");
            //sql.Append("c.CreatorRealName,");
            //sql.Append("Date( a.StatisticsTime ) StatisticalTime,");
            //sql.Append("a.StatisticsNums*ifnull(g.Weight,0) Num,");
            //sql.Append("ifnull( f.UnitPrice, 0 ) UnitPrice,");
            //sql.Append("(a.StatisticsNums*ifnull(g.Weight,0)*ifnull(f.UnitPrice,0)) Total ");
            //sql.Append("FROM ");
            //sql.Append("PB_JobBooking a ");
            //sql.Append("LEFT JOIN PB_Material g on a.MaterialId=g.Id ");
            //sql.Append("LEFT JOIN Base_User b on a.ReportUserId=b.Id ");
            //sql.Append("LEFT JOIN PB_ScheduleDetail c ON a.ReportUserId = c.UserId ");
            //sql.Append("AND Date( a.StatisticsTime )= c.WorkDate ");
            //sql.Append("LEFT JOIN PB_ShiftType d ON c.Type = d.Id ");
            //sql.Append("LEFT JOIN ( SELECT * FROM PB_MaterialPrice WHERE ProcecureSubid = '1463792840462045184' ) f ON a.MaterialId = f.Materialid ");
            //sql.Append("AND Date( a.StatisticsTime )= f.WorkDate ");
            //sql.Append("AND a.DeptId = f.Deptid ");
            //sql.Append("WHERE ");
            //sql.Append("a.DeptId = '" + data.DeptId + "' ");
            //sql.Append("AND Date( a.StatisticsTime ) BETWEEN '" + data.StartDate + "' AND '" + data.EndDate + "' ");
            //sql.Append(") t ");
            //sql.Append("GROUP BY ");
            //sql.Append("t.FactoryId,");
            //sql.Append("t.DeptId,");
            //sql.Append("t.Type,");
            //sql.Append("t.TypeName,");
            //sql.Append("t.ProcedureId,");
            //sql.Append("t.CreatorRealName,");
            //sql.Append("t.StatisticalTime,");
            //sql.Append("t.UnitPrice,");
            //sql.Append("t.ReportUserId,");
            //sql.Append("t.MaterialName ");
            #endregion
            #region 新sql
            sql.Append("SELECT ");
            sql.Append("a.DeptId FactoryId,");
            sql.Append("a.WorkShopId DeptId,");
            sql.Append("c.StaffName CreatorRealName,");
            sql.Append("c.TeamType Type,");
            sql.Append("d.Name TypeName,");
            sql.Append("'1463792840462045184' ProcedureId,");
            sql.Append("a.StatisticsTime StatisticalTime,");
            sql.Append("e.UnitPrice,");
            sql.Append("a.StatisticsNums*ifnull(f.Weight,0) Num,");
            sql.Append("a.StatisticsNums*ifnull(f.Weight,0)*e.UnitPrice Total,");
            sql.Append("a.MaterialName ");
            sql.Append("FROM ");
            sql.Append("PB_JobBooking a ");
            sql.Append("LEFT JOIN PB_DispatchGroup b ON a.CirBillId = b.CirBillId ");
            sql.Append("LEFT JOIN PB_Dispatch c ON b.DispatchId = c.Id ");
            sql.Append("LEFT JOIN PB_ShiftType d on c.TeamType=d.Id ");
            sql.Append("LEFT JOIN PB_MaterialPrice e on a.MaterialId=e.MaterialId AND e.ProcecureSubid='1463792840462045184' AND Date( a.ReportTime )=e.WorkDate ");
            sql.Append("LEFT JOIN PB_Material f on a.MaterialId=f.Id ");
            sql.Append("WHERE a.WorkShopId='" + data.DeptId + "'");
            sql.Append("AND Date( a.StatisticsTime ) BETWEEN '" + data.StartDate + "' AND '" + data.EndDate + "'");
            #endregion
            var listdata = await Db.GetListBySqlAsync<UpUserWagesDTO>(sql.ToString());
            #endregion

            List<PB_UserWages> wagesdata = new List<PB_UserWages>();
            foreach (var dr in listdata)
            {
                PB_UserWages wages = new PB_UserWages();
                wages.Id = IdHelper.GetId();
                wages.FactoryId = dr.FactoryId;
                wages.DeptId = dr.DeptId;
                wages.UserName = dr.CreatorRealName;
                wages.TeamType = dr.Type;
                wages.TeamName = dr.TypeName;
                wages.SubId = dr.ProcedureId;
                wages.WorkDate = Convert.ToDateTime(dr.StatisticalTime);
                wages.Price = Convert.ToDouble(dr.UnitPrice);
                wages.Nums = Convert.ToDouble(dr.Num);
                wages.TotalPrice = Convert.ToDouble(dr.Total);
                wages.CreatorId = _operator.UserId;
                wages.CreatorRealName = _operator.UserName;
                wages.CreateTime = DateTime.Now;
                wages.Deleted = false;
                wages.Type = 1;
                wages.PayMode = 0;
                wages.MaterialName = dr.MaterialName;
                wagesdata.Add(wages);
            }
            await Db.InsertAsync<PB_UserWages>(wagesdata);
        }

        /// <summary>
        /// 计算工资劳务(按车间)
        /// </summary>
        /// 日结：区间内手书总重/总工时*每吨单价*个人工时
        /// <param name="data"></param>
        /// <returns></returns>
        public async Task UpUserWagesForLabourServicesByDayAsync(WagesDTO data)
        {
            //日结统计
            StringBuilder sql=new StringBuilder();
            sql.Append("SELECT ");
            sql.Append("b.FactoryId,");
            sql.Append("b.DepartmentId,");
            sql.Append("b.RealName,");
            sql.Append("a.ProcecureSubId,");
            sql.Append("DATE(a.OperationDate) OperationDate,");
            sql.Append("e.UnitPrice,");
            sql.Append("a.Num,");
            sql.Append("d.Num TotalNum,");
            sql.Append("a.WeightTotal UserWeight,");
            sql.Append("d.TotalWeight TotalWeight,");
            sql.Append("d.TotalWeight*e.UnitPrice/d.Num*a.Num TotalPrice ");
            sql.Append("FROM ");
            sql.Append("PB_LabourServices a ");
            sql.Append("LEFT JOIN Base_User b ON a.OperatorId = b.Id ");
            sql.Append("LEFT JOIN PB_MaterialPrice e ON a.ProcecureSubId = e.ProcecureSubid ");
            sql.Append("AND e.WorkDate = date( a.OperationDate ) ");
            sql.Append("LEFT JOIN ( SELECT Items, sum( Num ) Num,sum(WeightTotal) TotalWeight FROM PB_LabourServices GROUP BY Items ");
            sql.Append(") d ON a.Items = d.Items ");
            sql.Append("WHERE a.Type=0 and ");
            sql.Append("a.SingleSettlement=0 and ");
            sql.Append("date(a.OperationDate) ");
            sql.Append("BETWEEN ");
            sql.Append("'"+data.StartDate+"'");
            sql.Append(" AND ");
            sql.Append("'" + data.EndDate + "'");
            var listdata = await Db.GetListBySqlAsync<PB_LabourServicesDTO>(sql.ToString());
            List<PB_UserWages> wagesdata = new List<PB_UserWages>();
            foreach (var dr in listdata)
            {
                PB_UserWages wages = new PB_UserWages();
                wages.Id = IdHelper.GetId();
                wages.FactoryId = dr.FactoryId;
                wages.DeptId = dr.DepartmentId;
                wages.UserName = dr.RealName;
                wages.TeamType = "";
                wages.TeamName = "";
                wages.SubId = dr.ProcecureSubId;
                wages.WorkDate = Convert.ToDateTime(dr.OperationDate);
                wages.Price = Convert.ToDouble(dr.UnitPrice);
                wages.Nums = Convert.ToDouble(dr.Num);
                wages.TotalPrice = Convert.ToDouble(dr.TotalPrice);
                wages.CreatorId = _operator.UserId;
                wages.CreatorRealName = _operator.UserName;
                wages.CreateTime = DateTime.Now;
                wages.Deleted = false;
                wages.Type = 2;
                wages.PayMode = 0;
                wages.MaterialName = "";
                wages.TotalWeight = dr.TotalWeight==null?0:dr.TotalWeight;
                wages.TotalNum = Convert.ToDouble(dr.TotalNum);
                wages.Proportion = Convert.ToInt32(wages.Nums / wages.TotalNum * 100);
                wages.UserWeight = dr.UserWeight == null ? 0 : dr.UserWeight;  
                wagesdata.Add(wages);
            }
            await Db.InsertAsync<PB_UserWages>(wagesdata);
        }

        /// <summary>
        /// 计算工资劳务(按车间)
        /// </summary>
        /// 日结：区间内手书总重/总工时*每吨单价*个人工时
        /// <param name="data"></param>
        /// <returns></returns>
        public async Task UpUserWagesForLabourServicesByMonthAsync(WagesDTO data)
        {
            string dateAreaBegin = Convert.ToDateTime(data.StartDate).ToString("yyyy-MM-01");
            string dateAreaEnd = Convert.ToDateTime(dateAreaBegin).AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");
            //月结统计
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT ");
            sql.Append("b.FactoryId,");
            sql.Append("b.DepartmentId,");
            sql.Append("b.RealName,");
            sql.Append("a.ProcecureSubId,");
            sql.Append("DATE(a.OperationDate) OperationDate,");
            sql.Append("a.Num,");
            //sql.Append("c.Num TotalNum ");
            sql.Append("0 TotalNum ");
            sql.Append("FROM ");
            sql.Append("PB_LabourServices a ");
            sql.Append("LEFT JOIN Base_User b ON a.OperatorId = b.Id ");
            //sql.Append("LEFT JOIN (");
            //sql.Append("SELECT ");
            //sql.Append("Items,");
            //sql.Append("sum( Num ) Num ");
            //sql.Append("FROM ");
            //sql.Append("PB_LabourServices ");
            //sql.Append("GROUP BY ");
            //sql.Append("Items");
            //sql.Append(") c on a.Items=c.Items ");
            sql.Append("WHERE a.Type=1 and ");
            sql.Append("a.SingleSettlement=0 ");
            sql.Append("AND date(a.OperationDate) ");
            sql.Append("BETWEEN ");
            sql.Append("'" + dateAreaBegin + "'");
            sql.Append(" AND ");
            sql.Append("'" + dateAreaEnd + "'");
            var listdata = await Db.GetListBySqlAsync<PB_LabourServicesDTO>(sql.ToString());
            //本月总工时
            double _totalNum= 0;
            foreach (var item in listdata) 
            {
                _totalNum+= item.Num.IsNullOrEmpty() ? 0 : Convert.ToDouble(item.Num);
            }
            List<PB_UserWages> wagesdata = new List<PB_UserWages>();
            foreach (var dr in listdata)
            {
                PB_UserWages wages = new PB_UserWages();
                wages.Id = IdHelper.GetId();
                wages.FactoryId = dr.FactoryId;
                wages.DeptId = dr.DepartmentId;
                wages.UserName = dr.RealName;
                wages.TeamType = "";
                wages.TeamName = "";
                wages.SubId = dr.ProcecureSubId;
                wages.WorkDate = Convert.ToDateTime(dr.OperationDate);
                wages.Price = Convert.ToDouble(data.Price);
                wages.Nums = Convert.ToDouble(dr.Num);
                //wages.TotalPrice = Convert.ToDouble(data.WeightTotal)* Convert.ToDouble(data.Price) / Convert.ToDouble(dr.TotalNum) * Convert.ToDouble(dr.Num);
                wages.TotalPrice = Convert.ToDouble(data.WeightTotal) * Convert.ToDouble(data.Price) / _totalNum * Convert.ToDouble(dr.Num);
                wages.CreatorId = _operator.UserId;
                wages.CreatorRealName = _operator.UserName;
                wages.CreateTime = DateTime.Now;
                wages.Deleted = false;
                wages.Type = 2;
                wages.PayMode = 1;
                //wages.TotalNum = Convert.ToDouble(dr.TotalNum);
                wages.TotalNum = _totalNum;
                wages.MaterialName = "";
                wages.TotalWeight = Convert.ToDouble(data.WeightTotal);
                wages.Proportion = Convert.ToInt32((wages.Nums/ Convert.ToDouble(wages.TotalNum))*100);
                wagesdata.Add(wages);
            }
            await Db.InsertAsync<PB_UserWages>(wagesdata);
        }

        /// <summary>
        /// 计算工资单笔结算（按车间）
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public async Task UpUserWagesForLabourServicesBySingleSettlementAsync(WagesDTO data)
        {
            //单笔结算统计
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT ");
            sql.Append("b.FactoryId,");
            sql.Append("b.DepartmentId,");
            sql.Append("b.RealName,");
            sql.Append("a.ProcecureSubId,");
            sql.Append("DATE(a.OperationDate) OperationDate,");
            sql.Append("c.UnitPrice,");
            sql.Append("a.Num,");
            sql.Append("c.Num TotalNum,");
            sql.Append("c.WeightTotal TotalWeight,");
            sql.Append("c.WeightTotal*c.UnitPrice TotalPrice ");
            sql.Append("FROM ");
            sql.Append("PB_LabourServices a ");
            sql.Append("LEFT JOIN Base_User b ON a.OperatorId = b.Id ");
            sql.Append("LEFT JOIN (");
            sql.Append("SELECT ");
            sql.Append("a.Items,");
            sql.Append("a.WeightTotal ,");
            sql.Append("b.UnitPrice,");
            sql.Append("sum( a.Num ) Num ");
            sql.Append("FROM ");
            sql.Append("PB_LabourServices a ");
            sql.Append("LEFT JOIN PB_MaterialPrice b on a.ProcecureSubId=b.ProcecureSubid and b.WorkDate=date(a.OperationDate) ");
            sql.Append("GROUP BY ");
            sql.Append("a.Items,");
            sql.Append("a.WeightTotal,");
            sql.Append("b.UnitPrice");
            sql.Append(") c on a.Items=c.Items ");
            //sql.Append("WHERE a.Type=0 and ");
            sql.Append("WHERE ");
            sql.Append("a.SingleSettlement=1 and ");
            sql.Append("date(a.OperationDate) ");
            sql.Append("BETWEEN ");
            sql.Append("'" + data.StartDate + "'");
            sql.Append(" AND ");
            sql.Append("'" + data.EndDate + "'");
            var listdata = await Db.GetListBySqlAsync<PB_LabourServicesDTO>(sql.ToString());
            List<PB_UserWages> wagesdata = new List<PB_UserWages>();
            foreach (var dr in listdata)
            {
                PB_UserWages wages = new PB_UserWages();
                wages.Id = IdHelper.GetId();
                wages.FactoryId = dr.FactoryId;
                wages.DeptId = dr.DepartmentId;
                wages.UserName = dr.RealName;
                wages.TeamType = "";
                wages.TeamName = "";
                wages.SubId = dr.ProcecureSubId;
                wages.WorkDate = Convert.ToDateTime(dr.OperationDate);
                wages.Price = Convert.ToDouble(dr.UnitPrice);
                wages.Nums = Convert.ToDouble(dr.Num);
                wages.TotalPrice = Convert.ToDouble(dr.TotalPrice);
                wages.CreatorId = _operator.UserId;
                wages.CreatorRealName = _operator.UserName;
                wages.CreateTime = DateTime.Now;
                wages.Deleted = false;
                wages.Type = 2;
                wages.PayMode = 0;
                wages.MaterialName = "";
                wages.TotalWeight = dr.TotalWeight == null ? 0 : dr.TotalWeight;
                wages.TotalNum = Convert.ToDouble(dr.TotalNum);
                wages.Proportion = 100;
                wagesdata.Add(wages);
            }
            await Db.InsertAsync<PB_UserWages>(wagesdata);
        }

        /// <summary>
        /// 工资(天)
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<UserWagesListDTO> GetUserWagesAsync(WagesConditionDTO input)
        {
            int index = (input.pageIndex - 1) * input.pageRows;
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("SELECT ");
            sqlStr.Append("ROW_NUMBER() over ( ORDER BY a.WorkDate DESC ) AS Rnumber,");
            sqlStr.Append("a.FactoryId,");
            sqlStr.Append("b.`Name` AS FactoryName,");
            sqlStr.Append("a.DeptId,");
            sqlStr.Append("c.`Name` AS DeptName,");
            sqlStr.Append("a.UserName,");
            sqlStr.Append("a.TeamType,");
            sqlStr.Append("a.TeamName,");
            sqlStr.Append("IFNULL(a.TotalWeight,0) TotalWeight,");
            sqlStr.Append("IFNULL(a.Proportion,0) Proportion,");
            sqlStr.Append("a.SubId,");
            sqlStr.Append("d.`Name` AS SubName,");
            sqlStr.Append("a.WorkDate,");
            sqlStr.Append("a.Price,");
            sqlStr.Append("a.Nums,");
            sqlStr.Append("a.TotalPrice,");
            sqlStr.Append("a.MaterialName,");
            sqlStr.Append("a.TotalNum,");
            sqlStr.Append("a.Type ");
            sqlStr.Append("FROM ");
            sqlStr.Append("PB_UserWages AS a ");
            sqlStr.Append("JOIN Base_Department AS b ON a.FactoryId = b.Id ");
            sqlStr.Append("JOIN Base_Department AS c ON a.DeptId = c.Id ");
            sqlStr.Append("JOIN PB_ProcecureSub AS d ON a.SubId = d.Id ");
            sqlStr.Append("WHERE 1=1 ");
            //sqlStr.Append("a.FactoryId='" + _operator.Property?.FactoryId + "' ");
            if (!input.StartDate.IsNullOrEmpty() && !input.EndDate.IsNullOrEmpty())
            {
                sqlStr.Append("AND a.WorkDate BETWEEN '" + input.StartDate + "' AND '" + input.EndDate + "' ");
            }
            if (!input.Type.IsNullOrEmpty())
            {
                if (input.Type == "2")
                {
                    sqlStr.Append("AND a.Type =" + input.Type + " ");
                }
                else
                {
                    sqlStr.Append("AND a.Type < 2 ");
                }
            }
            if (!input.PayMode.IsNullOrEmpty())
            {
                sqlStr.Append("AND a.PayMode = " + input.PayMode + " ");
            }
            if (!input.Keyword.IsNullOrEmpty())
            {
                sqlStr.Append("AND a.UserName like '%" + input.Keyword + "%' ");
            }
            if (!input.TeamType.IsNullOrEmpty())
            {
                sqlStr.Append("AND a.TeamName='" + input.TeamType + "' ");
            }
            if (!input.DeptId.IsNullOrEmpty())
            {
                sqlStr.Append("AND a.DeptId='" + input.DeptId + "' ");
            }
            if (!input.SubId.IsNullOrEmpty())
            {
                sqlStr.Append("AND d.Id='" + input.SubId + "' ");
            }
            if (!input.SortField.IsNullOrEmpty() && !input.SortType.IsNullOrEmpty())
            {
                sqlStr.Append("ORDER BY " + input.SortField + " " + input.SortType);
            }
            sqlStr.Append(" LIMIT "+ index + ","+ input.pageRows);
            List<PB_UserWagesDTO> listdata = await Db.GetListBySqlAsync<PB_UserWagesDTO>(sqlStr.ToString());
            sqlStr.Clear();
            sqlStr.Append("SELECT ");
            sqlStr.Append("COUNT(*),");
            sqlStr.Append("sum(TotalPrice) ");
            sqlStr.Append("FROM ");
            sqlStr.Append("PB_UserWages ");
            sqlStr.Append("WHERE 1=1 ");
            //sqlStr.Append("a.FactoryId='" + _operator.Property?.FactoryId + "' ");
            if (!input.StartDate.IsNullOrEmpty() && !input.EndDate.IsNullOrEmpty())
            {
                sqlStr.Append("AND WorkDate BETWEEN '" + input.StartDate + "' AND '" + input.EndDate + "' ");
            }
            if (!input.Type.IsNullOrEmpty())
            {
                if (input.Type == "2")
                {
                    sqlStr.Append("AND Type =" + input.Type + " ");
                }
                else
                {
                    sqlStr.Append("AND Type < 2 ");
                }
            }
            if (!input.PayMode.IsNullOrEmpty())
            {
                sqlStr.Append("AND PayMode = " + input.PayMode + " ");
            }
            if (!input.Keyword.IsNullOrEmpty())
            {
                sqlStr.Append("AND UserName like '%" + input.Keyword + "%' ");
            }
            if (!input.TeamType.IsNullOrEmpty())
            {
                sqlStr.Append("AND TeamName='" + input.TeamType + "' ");
            }
            if (!input.DeptId.IsNullOrEmpty())
            {
                sqlStr.Append("AND DeptId='" + input.DeptId + "' ");
            }
            if (!input.SubId.IsNullOrEmpty())
            {
                sqlStr.Append("AND SubId='" + input.SubId + "' ");
            }

            DataTable dt = await GetDataTableWithSqlAsync(sqlStr.ToString());

            UserWagesListDTO data = new UserWagesListDTO();
            data.Count = Convert.ToInt32(dt.Rows[0][0]);
            data.Total = 0;
            if (!dt.Rows[0][1].IsNullOrEmpty())
            {
                data.Total = Convert.ToDouble(dt.Rows[0][1]);
            }
            data.UserWages = listdata;
            return data;
        }

        /// <summary>
        /// 获取工序信息
        /// </summary>
        /// <returns></returns>
        public async Task<List<PB_ProcecureSub>> GetProcecureSubAsync()
        {
            List<string> liststr = await _opdept.GetFactoryIds();
            string sqlMain = @"select b.* from PB_ProcecureMain as a left join PB_ProcecureSub as b on a.Id=b.ParentId where a.DeptId in (" + string.Join(",", liststr.ToArray()) + ")";
            List<PB_ProcecureSub> listdata = await Db.GetListBySqlAsync<PB_ProcecureSub>(sqlMain);
            return listdata;
        }

        /// <summary>
        /// 获取班别信息
        /// </summary>
        /// <param name="DeptId">车间Id</param>
        /// <returns></returns>
        public async Task<List<PB_ShiftType>> GetShiftTypeAsync(string DeptId)
        {
            List<PB_ShiftType> listdata = await Db.GetIQueryable<PB_ShiftType>().Where(x => x.WorkShopID == DeptId && x.Deleted == false).ToListAsync();
            return listdata;
        }

        /// <summary>
        /// 获取登录人所属工厂下的车间信息
        /// </summary>
        /// <param name="deptId">工厂Id</param>
        /// <returns></returns>
        public async Task<List<Base_Department>> GetWorkShopAsync(string deptId)
        {
            List<string> liststr = new List<string>();
            if (deptId.IsNullOrEmpty())
            {
                liststr = await _opdept.GetWorkshopString(_operator.Property.FactoryId);
            }
            else
            {
                liststr = await _opdept.GetWorkshopString(deptId);
            }
          
            List<Base_Department> listdata = await Db.GetIQueryable<Base_Department>().Where(x => liststr.Contains(x.Id)).ToListAsync();
            return listdata;
        }
        /// <summary>
        /// 获取登录人所属工厂下的部门
        /// </summary>
        /// <param name="deptId">工厂Id</param>
        /// <returns></returns>
        public async Task<List<Base_Department>> GetBumenAsync(string deptid)
        {
            List<string> liststr = new List<string>();
            if (deptid.IsNullOrEmpty())
            {
                liststr = await _opdept.GetBumenString(_operator.Property.FactoryId);
            }
            else
            {
                liststr = await _opdept.GetBumenString(deptid);
            }

            List<Base_Department> listdata = await Db.GetIQueryable<Base_Department>().Where(x => liststr.Contains(x.Id)).ToListAsync();
            return listdata;
        }
        /// <summary>
        /// 获取登录人所属工厂
        /// </summary>
        /// <returns></returns>
        public async Task<List<Base_Department>> GetFactoryAsync()
        {
            List<Base_Department> listdata = await Db.GetIQueryable<Base_Department>().Where(x => x.Id == _operator.Property.FactoryId).ToListAsync();
            return listdata;
        }

        /// <summary>
        /// 获取工资详细
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<List<PB_UserWagesDTO>> GetExportUserWagesAsync(WagesConditionDTO input)
        {
            int index = (input.pageIndex - 1) * input.pageRows + 1;
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append(" select * from ( SELECT ROW_NUMBER()over(order by a.SubId) as Rnumber,a.FactoryId,b.`Name` as FactoryName,a.DeptId,c.`Name` as DeptName,a.UserName,a.TeamType,a.TeamName,a.SubId,d.`Name` as SubName,a.WorkDate,a.Price,a.Nums,a.TotalPrice ");
            sqlStr.Append(" FROM PB_UserWages as a ");
            sqlStr.Append(" join Base_Department as b on a.FactoryId=b.Id ");
            sqlStr.Append(" join Base_Department as c on a.DeptId=c.Id ");
            sqlStr.Append(" join PB_ProcecureSub as d on a.SubId=d.Id ");
            sqlStr.Append(" where a.FactoryId='" + _operator.Property?.FactoryId + "' ");
            if (!input.StartDate.IsNullOrEmpty() && !input.EndDate.IsNullOrEmpty())
            {
                sqlStr.Append(" and a.WorkDate BETWEEN '" + input.StartDate + "' and '" + input.EndDate + "' ");
            }
            if (!input.Keyword.IsNullOrEmpty())
            {
                sqlStr.Append(" and ( a.UserName like '%" + input.Keyword + "%' ) ");
            }
            if (!input.TeamType.IsNullOrEmpty())
            {
                sqlStr.Append(" and a.TeamName='" + input.TeamType + "' ");
            }
            if (!input.DeptId.IsNullOrEmpty())
            {
                sqlStr.Append(" and a.DeptId='" + input.DeptId + "' ");
            }
            if (!input.SubId.IsNullOrEmpty())
            {
                sqlStr.Append(" and d.Id='" + input.SubId + "' ");
            }
            sqlStr.Append(" ) as t where Rnumber BETWEEN '" + index + "' AND (" + input.pageIndex * input.pageRows + ")");
            List<PB_UserWagesDTO> listdata = await Db.GetListBySqlAsync<PB_UserWagesDTO>(sqlStr.ToString());
            return listdata;
        }

        /// <summary>
        /// 获取所有工艺下的工序
        /// </summary>
        /// <returns></returns>
        public async Task<List<PB_ProcecureSub>> GetProcecureSubList()
        {
       
            string sqlMain = @"select b.* from PB_ProcecureMain as a INNER join PB_ProcecureSub as b on a.Id=b.ParentId ";
            List<PB_ProcecureSub> listdata = await Db.GetListBySqlAsync<PB_ProcecureSub>(sqlMain);
            return listdata;
        }



        #endregion

        #region 私有成员

        #endregion
    }
}